﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;
using Ortodoctor.Componentes;

namespace Ortodoctor.modelo
{
    class modeloUsuario : conexao
    {
        public modeloUsuario()
        {
            conn = new NpgsqlConnection(connectionstring);
            conn.Open();
        }

        public int insert(Usuario u)
        {
            try
            {
                //con.open();
                String sql = "INSERT INTO usuario(id_clinica, login, senha, nivel_acesso) " +
                             "VALUES (:p1, :p2, :p3, :p4)";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", u.IdClinica);
                com.Parameters.AddWithValue("p2", u.Login);
                com.Parameters.AddWithValue("p3", u.Senha);
                com.Parameters.AddWithValue("p4", u.NivelAcesso);
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
            return getMaxID();
        }

        public DataTable getAll()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM USUARIO INNER JOIN CLINICA USING(ID_CLINICA) INNER JOIN ENDERECO USING(ID_ENDERECO)";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public int getMaxID()
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT max(id_usuario) as max FROM usuario";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt.Rows.Count == 0 ? 0 : Convert.ToInt32(dt.Rows[0]["max"]);
        }

        public DataTable getUsuario(int id)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT * FROM usuario INNER JOIN clinica USING(id_clinica) " +
                         "WHERE usuario.id_usuario=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public void update(Usuario u)
        {
            try
            {
                //con.open();
                String sql = "UPDATE usuario SET id_clinica=:p1, login=:p2, senha=:p3, nivel_acesso=:p4, id_usuario_modificacao=:id_mod " +
                             "WHERE id_usuario=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", u.IdClinica);
                com.Parameters.AddWithValue("p2", u.Login);
                com.Parameters.AddWithValue("p3", u.Senha);
                com.Parameters.AddWithValue("p4", u.NivelAcesso);
                com.Parameters.AddWithValue("id", u.IdUsuario);
                com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void delete(int id)
        {
            try
            {
                //con.open();
                String sql = "DELETE FROM usuario WHERE id_usuario=:id";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("id", id);
                com.ExecuteNonQuery();

                //sql = "update usuario_hist set id_usuario=:id_mod, data_modificacao=:data WHERE id_usuario=:id";
                //com = new NpgsqlCommand(sql, conn);
                //com.Parameters.AddWithValue("id", id);
                //com.Parameters.AddWithValue("id_mod", System.Web.HttpContext.Current.Session["ID_USUARIO_ACESSO"].ToString());
                //com.Parameters.AddWithValue("data", DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss"));
                //com.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable autenticaUsuario(string login, string senha)
        {
            DataTable dt = new DataTable();
            try
            {
                //con.open();
                String sql = "SELECT usu.* " +
                               "FROM usuario usu " +
                              "WHERE login = :login and senha = :senha";
                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("login", login);
                com.Parameters.AddWithValue("senha", senha);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(com);
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public void Log(string operacao, string log)
        {
            DateTime dt = DateTime.Now;
            try
            {
                String sql = @"INSERT INTO usuario_log(operacao, log, data) values (:p1, :p2, :p3)";

                NpgsqlCommand com = new NpgsqlCommand(sql, conn);
                com.Parameters.AddWithValue("p1", operacao);
                com.Parameters.AddWithValue("p2", log);
                com.Parameters.AddWithValue("p3", DateTime.Now.ToShortDateString() + " - " + DateTime.Now.ToShortTimeString());
                com.ExecuteScalar();
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
